﻿using SPI_TOOLS.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// Services/DatabaseService.cs
using MySql.Data.MySqlClient;
using System.Collections.ObjectModel;

namespace SPI_TOOLS.Services
{
    public class DatabaseService
    {
        public async Task<bool> TestConnectionAsync(DatabaseConnection connection)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception($"连接失败: {ex.Message}");
            }
        }

        public async Task<List<string>> GetDatabasesAsync(DatabaseConnection connection)
        {
            var databases = new List<string>();

            try
            {
                using var conn = new MySqlConnection($"Server={connection.Server};Port={connection.Port};Uid={connection.UserId};Pwd={connection.Password};");
                await conn.OpenAsync();

                using var cmd = new MySqlCommand("SHOW DATABASES", conn);
                using var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    databases.Add(reader.GetString(0));
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"获取数据库列表失败: {ex.Message}");
            }

            return databases;
        }

        public async Task<List<TableInfo>> GetTablesAsync(DatabaseConnection connection)
        {
            var tables = new List<TableInfo>();

            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = @"
                SELECT 
                    TABLE_NAME,
                    TABLE_SCHEMA,
                    TABLE_TYPE,
                    TABLE_ROWS,
                    CREATE_TIME,
                    UPDATE_TIME
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = @database
                ORDER BY TABLE_NAME";

                using var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@database", connection.Database);

                using var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    tables.Add(new TableInfo
                    {
                        TableName = reader.GetString("TABLE_NAME"),
                        TableSchema = reader.GetString("TABLE_SCHEMA"),
                        TableType = reader.GetString("TABLE_TYPE"),
                        TableRows = reader.IsDBNull("TABLE_ROWS") ? null : reader.GetInt64("TABLE_ROWS"),
                        CreateTime = reader.IsDBNull("CREATE_TIME") ? null : reader.GetDateTime("CREATE_TIME"),
                        UpdateTime = reader.IsDBNull("UPDATE_TIME") ? null : reader.GetDateTime("UPDATE_TIME")
                    });
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"获取表列表失败: {ex.Message}");
            }

            return tables;
        }

        public async Task<List<ColumnInfo>> GetTableColumnsAsync(DatabaseConnection connection, string tableName)
        {
            var columns = new List<ColumnInfo>();

            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = @"
                SELECT 
                    COLUMN_NAME,
                    DATA_TYPE,
                    IS_NULLABLE,
                    COLUMN_KEY,
                    COLUMN_DEFAULT,
                    EXTRA,
                    CHARACTER_MAXIMUM_LENGTH,
                    COLUMN_COMMENT
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = @database AND TABLE_NAME = @tableName
                ORDER BY ORDINAL_POSITION";

                using var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@database", connection.Database);
                cmd.Parameters.AddWithValue("@tableName", tableName);

                using var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    columns.Add(new ColumnInfo
                    {
                        ColumnName = reader.GetString("COLUMN_NAME"),
                        DataType = reader.GetString("DATA_TYPE"),
                        IsNullable = reader.GetString("IS_NULLABLE"),
                        ColumnKey = reader.GetString("COLUMN_KEY"),
                        ColumnDefault = reader.IsDBNull("COLUMN_DEFAULT") ? "" : reader.GetString("COLUMN_DEFAULT"),
                        Extra = reader.GetString("EXTRA"),
                        CharacterMaximumLength = reader.IsDBNull("CHARACTER_MAXIMUM_LENGTH") ?
                            null : reader.GetInt64("CHARACTER_MAXIMUM_LENGTH"),
                        ColumnComment = reader.GetString("COLUMN_COMMENT")
                    });
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"获取表结构失败: {ex.Message}");
            }

            return columns;
        }

        public async Task<TableData> GetTableDataAsync(DatabaseConnection connection, string tableName, string? whereClause = null)
        {
            var tableData = new TableData();

            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = $"SELECT * FROM `{tableName}`";
                if (!string.IsNullOrEmpty(whereClause))
                {
                    sql += $" WHERE {whereClause}";
                }
                sql += " LIMIT 1000"; // 限制返回1000条记录

                using var cmd = new MySqlCommand(sql, conn);
                using var reader = await cmd.ExecuteReaderAsync();

                // 获取列名
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    tableData.ColumnNames.Add(reader.GetName(i));
                }

                // 获取数据
                while (await reader.ReadAsync())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var columnName = reader.GetName(i);
                        var value = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
                        row[columnName] = value;
                    }
                    tableData.Rows.Add(row);
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"获取表数据失败: {ex.Message}");
            }

            return tableData;
        }

        public async Task<int> ExecuteQueryAsync(DatabaseConnection connection, string query)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                using var cmd = new MySqlCommand(query, conn);
                return await cmd.ExecuteNonQueryAsync();
            }
            catch (Exception ex)
            {
                throw new Exception($"执行查询失败: {ex.Message}");
            }
        }

        public async Task<DataTable> ExecuteReaderAsync(DatabaseConnection connection, string query)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                using var cmd = new MySqlCommand(query, conn);
                using var reader = await cmd.ExecuteReaderAsync();

                var dataTable = new DataTable();
                dataTable.Load(reader);
                return dataTable;
            }
            catch (Exception ex)
            {
                throw new Exception($"执行查询失败: {ex.Message}");
            }
        }

        public async Task<bool> DeleteRecordsAsync(DatabaseConnection connection, string tableName, string whereClause)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = $"DELETE FROM `{tableName}` WHERE {whereClause}";
                using var cmd = new MySqlCommand(sql, conn);

                var affectedRows = await cmd.ExecuteNonQueryAsync();
                return affectedRows > 0;
            }
            catch (Exception ex)
            {
                throw new Exception($"删除记录失败: {ex.Message}");
            }
        }

        public async Task<bool> AddColumnAsync(DatabaseConnection connection, string tableName, string columnName,
            string dataType, bool isNullable, string? defaultValue = null, string? comment = null)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = $"ALTER TABLE `{tableName}` ADD COLUMN `{columnName}` {dataType}";

                if (!isNullable)
                    sql += " NOT NULL";

                if (!string.IsNullOrEmpty(defaultValue))
                    sql += $" DEFAULT {defaultValue}";

                if (!string.IsNullOrEmpty(comment))
                    sql += $" COMMENT '{comment.Replace("'", "''")}'";

                using var cmd = new MySqlCommand(sql, conn);
                await cmd.ExecuteNonQueryAsync();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception($"添加列失败: {ex.Message}");
            }
        }

        public async Task<bool> DropColumnAsync(DatabaseConnection connection, string tableName, string columnName)
        {
            try
            {
                using var conn = new MySqlConnection(connection.ConnectionString);
                await conn.OpenAsync();

                var sql = $"ALTER TABLE `{tableName}` DROP COLUMN `{columnName}`";
                using var cmd = new MySqlCommand(sql, conn);
                await cmd.ExecuteNonQueryAsync();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception($"删除列失败: {ex.Message}");
            }
        }
    }
}
